from quant_researcher.quant.project_tool.time_tool import get_yesterday
from quant_researcher.quant.datasource_fetch.crypto_api.glassnode import get_prices
from quant_researcher.quant.datasource_fetch.crypto_api.binance import fetch_binance_margin_interest_rate
import numpy as np
import os
import pandas as pd
import time
import datetime
from dateutil.tz import tzutc
from utils import df_into_db


end_date = get_yesterday(marker='with_n_dash')  # 计算截止昨日收盘
price_log_price = get_prices(ohlc=False, asset='BTC', start_date='2018-01-01', end_date=end_date)
price_log_price['log_prices'] = np.log10(price_log_price['close'])
DATA_DIR = f'E:\\指标数据'  # 用于存放测试数据

# 分析币安杠杆交易费率
file_path = os.path.join(DATA_DIR, f'margin_interest_rate')
os.makedirs(file_path, exist_ok=True)
file_name = os.path.join(file_path, f'binance_margin_interest_rate')
if os.path.exists(f'{file_name}.xlsx'):
    history_binance_margin_interest_rate = pd.read_excel(f'{file_name}.xlsx', index_col='end_date')
    latest_date = history_binance_margin_interest_rate.index[-7]
    start = time.mktime(datetime.datetime(int(latest_date[:4]), int(latest_date[5:7]), int(latest_date[-2:]), 0, 0, 0, tzinfo=tzutc()).timetuple())
else:
    start = time.mktime(datetime.datetime(2018, 1, 1, 0, 0, 0, tzinfo=tzutc()).timetuple())
end = time.mktime(datetime.datetime(int(end_date[:4]), int(end_date[5:7]), int(end_date[-2:]), 23, 59, 59, tzinfo=tzutc()).timetuple())
df_list = []
for asset in ['USDT', 'BUSD', 'BTC', 'ETH']:
    print(f'开始从binance获取{asset}的借贷费率')
    frame = fetch_binance_margin_interest_rate(asset=asset, start_time=start, end_time=end)  # https://www.binance.com/en/margin/interest-history
    frame['start_time'] = frame.apply(lambda x: int(x['timestamp'] / 1000), axis=1)
    frame['end_date'] = pd.to_datetime(frame['start_time'], unit='s').dt.tz_localize('UTC').dt.tz_convert('+0000')
    frame['end_date'] = frame['end_date'].dt.strftime('%Y-%m-%d')
    frame['datetime'] = pd.to_datetime(frame['start_time'], unit='s').dt.tz_localize('UTC').dt.tz_convert('+0000')
    frame['datetime'] = frame['datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')
    frame = frame[~frame.duplicated(subset='end_date')]  # 一天可能有多个费率数据
    frame.set_index('end_date', inplace=True)
    frame.rename(columns={'margin_interest_rate': f'{asset}_margin_interest_rate'}, inplace=True)
    df_list.append(frame[f'{asset}_margin_interest_rate'])
frame = pd.concat(df_list, axis=1)
all_new_frame = frame.merge(price_log_price, left_index=True, right_index=True)
if os.path.exists(f'{file_name}.xlsx'):
    all_new_frame = pd.concat([history_binance_margin_interest_rate, all_new_frame], axis=0)
    all_new_frame = all_new_frame[~all_new_frame.index.duplicated(keep='last')]  # index去重
# all_new_frame.to_excel(f'{file_name}.xlsx')
all_new_frame.fillna(0, inplace=True)
all_new_frame = all_new_frame.reset_index(drop=False)
df_into_db(all_new_frame, db_name="margin_interest_rate", table_name="binance_margin_interest_rate")
